package com.saunter.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import com.saunter.extendMapper.SalaryExtendMapper;
import com.saunter.extendModel.SalaryExtend;
import com.saunter.extendModel.UserExtend;
import com.saunter.model.Salary;
import com.saunter.model.SalaryKey;
import com.saunter.service.user.UserService;
import com.saunter.utils.Util;
import com.saunter.utils.excel.ExcelData;
import com.saunter.utils.excel.ExportExcelUtils;
import com.saunter.utils.excel.ImportExcelUtils;
import com.saunter.utils.exception.ExceptionManager;
import com.saunter.utils.response.CustomPage;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Service("SalaryService")
public class SalaryService {
    @Autowired
    private SalaryExtendMapper salaryExtendMapper;
    @Autowired
    private UserService userService;
    @Autowired
    private SalaryKeyService salaryKeyService;
    @Resource
    private ExceptionManager exceptionManager;

    public Boolean checkSalaryKey(Salary salary) {
        Map<String, Object> data = new HashMap<String, Object>();
        try {
            data = JSON.parseObject(salary.getSalaryData());
        }catch (Exception e) {
            throw exceptionManager.create("Prm003");
        }
        return salaryKeyService.checkIdKeys(new ArrayList<String>(data.keySet()));
    }
    public Boolean checkExistByIdNumAndName(String user_id_num, Date salary_date) {
        return 0 != salaryExtendMapper.getByIdNumAndDate(user_id_num, salary_date).size();
    }
    public int addSingle(Salary salary) {
        String salary_data = salary.getSalaryData();
        if(salary_data.equals("{}") || salary_data.equals("")) {
            throw exceptionManager.create("Sal002");
        }
        String user_id_num = salary.getUserIdNum();
        if(null == userService.getByIdNum(user_id_num)) {
            throw exceptionManager.create("User002");
        }
        if(this.checkExistByIdNumAndName(user_id_num, salary.getSalaryDate())) {
            throw exceptionManager.create("Sal001");
        }
        if(!this.checkSalaryKey(salary)) {
            throw exceptionManager.create("Prm003");
        }

        return salaryExtendMapper.insert(salary);
    }

    public int editSalary(Salary salary) {
        if(null == salaryExtendMapper.selectByPrimaryKey(salary.getId())) {
            throw exceptionManager.create("Sal003");
        }
        Date salary_date = salary.getSalaryDate();
        if(null != salary_date && this.checkExistByIdNumAndName(salary.getUserIdNum(), salary_date)) {
            throw exceptionManager.create("Sal001");
        }
        if(null != salary.getSalaryData() && !this.checkSalaryKey(salary)) {
            throw exceptionManager.create("Prm003");
        }
        return salaryExtendMapper.updateByPrimaryKeySelective(salary);
    }

    public int deleteById(int id) {
        if(null == salaryExtendMapper.selectByPrimaryKey(id)) {
            throw exceptionManager.create("Sal003");
        }
        return salaryExtendMapper.deleteByPrimaryKey(id);
    }

    @Transactional
    public int deleteByIds(List<String> ids) {
        if(ids.size() == salaryExtendMapper.deleteByIds(ids)) {
            return 1;
        }else {
            throw exceptionManager.create("Sal003");
        }
    }

    public int deleteByUserIdNum(String user_id_num) {
        return salaryExtendMapper.deleteByUserIdNum(user_id_num);
    }

    public CustomPage<SalaryExtend> getAll(int current_page, int page_size, Map<String, Object> filters) {
        PageHelper.startPage(current_page,page_size);
        List<SalaryExtend> salaries = salaryExtendMapper.getAll(filters);
        return new CustomPage<SalaryExtend>(salaries);
    }

    /**
     * 导出记录
     * @param response
     * @param filters
     * @return
     * @throws Exception
     */
    public int exportRecords(HttpServletResponse response, Map<String, Object> filters, Boolean operator_admin) throws Exception {
        List<SalaryExtend> salarys = salaryExtendMapper.getAll(filters);
        ExcelData excelData = new ExcelData();
        excelData.setName("record");

        List<String> titles = new ArrayList<String>(){{
            add("序号");add("工号");add("姓名");add("日期");
        }};

        List<SalaryKey> salary_keys;
        if(operator_admin) {
            salary_keys = salaryKeyService.getAllOn();
            titles.add("添加时间");
            titles.add("状态");
        }else {
            salary_keys = salaryKeyService.getAllShow();
        }

        List<String> salary_key_idkey = new ArrayList<String>();
        List<String> salary_key_label = new ArrayList<String>();
        for(SalaryKey salaryKey : salary_keys) {
            salary_key_idkey.add(salaryKey.getIdKey());
            salary_key_label.add(salaryKey.getLabel());
        }
        titles.addAll(4, salary_key_label);
        excelData.setTitles(titles);

        List<List<Object>> data_body = new ArrayList<List<Object>>();
        List<Object> row_data;

        SalaryExtend salary;
        int data_title_length = salary_keys.size();
        Map<String, Object> salary_data;

        //用来格式化Timestamp，否则会在最后添加";0"字符
        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for(int i_index = 0; i_index < salarys.size(); i_index ++) {
            salary = salarys.get(i_index);
            row_data = new ArrayList<Object>();
            row_data.add(i_index + 1);
            row_data.add(salary.getUserIdNum());
            row_data.add(salary.getUser_name());
            row_data.add(salary.getSalaryDate());

            salary_data = JSON.parseObject(salary.getSalaryData());
            for(int j_index = 0; j_index < data_title_length; j_index ++) {
                row_data.add(salary_data.get(salary_key_idkey.get(j_index)));
            }

            if(operator_admin) {
                row_data.add(sdf.format(salary.getAddTime()));
                row_data.add(salary.getStatus().equals(2)?"隐藏":"显示");
            }

            data_body.add(row_data);
        }
        excelData.setRows(data_body);
        ExportExcelUtils.exportExcel(response,"record.xlsx",excelData);
        return 1;
    }

    public int importRecords(MultipartFile file, Date salary_date) throws IOException {
        ExcelData excelData = ImportExcelUtils.loadExcelFile(file.getInputStream(), "record");
        if(null == excelData) {
            throw  exceptionManager.create("File005");
        }else {
            Map<String, String[]> excel_right_data = this.checkExcelAndGetRecordDatas(excelData, salary_date);
            salaryExtendMapper.insertByMap(excel_right_data, salary_date);
        }
        return 1;
    }

    /**
     * 检测excelData的数据的正确性
     * @param excelData
     * @param salary_date
     * @return 正确的可插入数据库的记录数据
     */
    public Map<String, String[]> checkExcelAndGetRecordDatas(ExcelData excelData, Date salary_date) {
        //获取salary_key的信息，用于之后的检测
        List<SalaryKey> salaryKeys = salaryKeyService.getAllOn();
        List<String> id_kes = new ArrayList<String>(), labels = new ArrayList<String>();
        for(SalaryKey salaryKey : salaryKeys) {
            id_kes.add(salaryKey.getIdKey());
            labels.add(salaryKey.getLabel());
        }

        //Excel标题检测（带顺序检测）
        List<String> excel_title = excelData.getTitles();
        if(!("序号".equals(excel_title.get(0)) && "工号".equals(excel_title.get(1)) && labels.equals(excel_title.subList(2, excel_title.size())))){
            throw exceptionManager.create("Sal004");
        }

        //导入的有效数据：经过id_num去重和非空检验；String[0]是行的序号；String[1]是salary_data，如果有错则为空
        Map<String, String[]> serialize_data = new HashMap<String, String[]>();
        //错误信息：key是行的序号，可能重复；value是具体错误
        List<String[]> err_info = new ArrayList<String[]>();
        //判断整个检测过程有无错误
        boolean has_error = false;
        //判断每行的user_id_num是否重复
        boolean id_num_repeat = false;
        //暂存行的值
        List<Object> excel_row;
        //暂存列的值和列的序号（可能与列的index不相等）
        Object cell_data;
        String cell_str, excel_index="";
        //暂存导入的salary_data，为之后的JSON序列化提供数据
        Map<String, String> salary_data;

        List<List<Object>> excel_rows = excelData.getRows();

        //为了提高效率都用基本的for循环
        for(int row_index = 0; row_index < excel_rows.size(); row_index ++ ) {
            excel_row = excel_rows.get(row_index);
            salary_data = new HashMap<String, String>();
            id_num_repeat = false;
            for (int cell_index = 0; cell_index < excel_row.size(); cell_index ++ ) {
                cell_data = excel_row.get(cell_index);
                if(null == cell_data || "".equals(cell_data.toString().trim())) {
                    has_error = true;
                    if(0 == cell_index) {
                        excel_index="行"+(row_index + 1);
                    }
                    err_info.add(new String[]{excel_index, (cell_index==0?"序号":cell_index==1?"工号":labels.get(cell_index-2))+"为空"});
                }else {
                    cell_str =  cell_data.toString().trim();
                    if(0 == cell_index) {
                        excel_index = cell_str;
                    }else if(1 == cell_index) {
                        //id_num去重检测
                        if(serialize_data.containsKey(cell_str)) {
                            has_error = true;
                            id_num_repeat = true;
                            err_info.add(new String[]{excel_index, "该行的工号与序号为"+serialize_data.get(cell_str)[0]+"的行重复"});
                        }
                    }else if(!has_error) {//为了效率，当检测不通过时不再组装具体数据
                        salary_data.put(id_kes.get(cell_index-2), cell_str);
                    }
                }
            }
            if(!id_num_repeat && null != excel_row.get(1) && !"".equals(excel_row.get(1).toString().trim())) {//重复行不再添加
                String user_id_num = excel_row.get(1).toString();
                //组装有效的数据
                if(has_error) {
                    serialize_data.put(user_id_num, new String[]{excel_index});
                }else {
                    serialize_data.put(user_id_num, new String[]{excel_index, JSON.toJSONString(salary_data)});
                }

            }
        }

        //用户id_num的存在检测
        List<String> excel_id_num = new ArrayList<String>(serialize_data.keySet());
        List<String> excel_right_id_num = userService.getIdNumByIdNum(excel_id_num);
        excel_id_num.removeAll(excel_right_id_num); //差集
        if(0 < excel_id_num.size()) {
            has_error = true;
            for(int i_index = 0; i_index < excel_id_num.size(); i_index ++) {
                String id_num = excel_id_num.get(i_index);
                err_info.add(new String[]{serialize_data.get(id_num)[0], "工号"+id_num+"的用户不存在"});
            }
        }

        //重复记录检测
        excel_right_id_num = salaryExtendMapper.getIdNumByIdNumAndDate(excel_right_id_num, salary_date);
        if(0 < excel_right_id_num.size()) {
            has_error = true;
            for(int i_index = 0; i_index < excel_right_id_num.size(); i_index ++) {
                String id_num = excel_right_id_num.get(i_index);
                err_info.add(new String[]{serialize_data.get(id_num)[0], "工号"+id_num+"的用户已存在该日期的记录"});
            }
        }
        if(has_error) {
            //按行的序号排序
            Collections.sort(err_info, new Comparator<String[]>() {
                @Override
                public int compare(String[] o1, String[] o2) {
                    try {
                        return (Double.parseDouble(o1[0]) > Double.parseDouble(o2[0])) ? 1 : -1;
                    }catch (Exception e) {
                        return o1[0].compareTo(o2[0]);
                    }
                }
            });
            throw exceptionManager.create("File004", err_info);
        }
        return serialize_data;
    }

    public List<SalaryExtend> formatSalaryData(List<SalaryExtend> salary_records) {
        List<String> salary_key_show = salaryKeyService.getAllIdKeysShow();
        Map<String, Object> temp_data;
        SalaryExtend temp_salary;
        String salary_key;
        JSONObject old_salary_data;
        for(int i_index = 0; i_index < salary_records.size(); i_index ++) {
            temp_salary = salary_records.get(i_index);
            old_salary_data = JSONObject.parseObject(temp_salary.getSalaryData());
            temp_data = new HashMap<String, Object>();
            for(int j_index = 0; j_index < salary_key_show.size(); j_index ++) {
                salary_key = salary_key_show.get(j_index);
                temp_data.put(salary_key, old_salary_data.get(salary_key));
            }
            temp_salary.setSalaryData(JSON.toJSONString(temp_data));

            temp_salary.setAddTime(null);
            temp_salary.setStatus(null);
        }
        return salary_records;
    }
}
